package io.renren.common.utils.excel;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import cn.afterturn.easypoi.util.PoiPublicUtil;
import cn.hutool.core.collection.CollectionUtil;
import io.renren.common.exception.CustomException;
import io.renren.common.utils.DateUtils;
import io.renren.modules.impexp.controller.ResponseStatusEnum;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.springframework.beans.BeanUtils;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.util.*;
import java.util.stream.Collectors;

/**
 * @description: Excel处理
 * @author: wurj
 * @Date: 2022/2/15 23:58:58
 */
@Slf4j
public class ExcelUtils {
    
    private static final short FONT_SIZE_ELEVEN = 11;
    
    /**
     * 导出Excel
     *
     * @param response
     * @param fileName
     * @param list
     * @param pojoClass
     * @throws IOException
     */
    public static void exportExcel(HttpServletResponse response, String fileName, Collection<?> list,
                                   Class<?> pojoClass) throws IOException {
        if (StringUtils.isBlank(fileName)) {
            fileName = DateUtils.format(new Date());
        }
        ExportParams exportParams = new ExportParams();
        exportParams.setStyle(ExcelStyleUtil.class);
//        exportParams.setSheetName("export");
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
//        workbook.getSheet("export");
        
        response.setCharacterEncoding("UTF-8");
        response.setHeader("content-Type", "application/vnd.ms-excel");
        response.setHeader("Content-Disposition",
                "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") + ".xls");
        ServletOutputStream out = response.getOutputStream();
        workbook.write(out);
        out.flush();
        workbook.close();
    }
    
    
    
    /**
     * 导出Excel 带下拉框
     *
     * @param response
     * @param fileName
     * @param list
     * @param pojoClass
     * @throws IOException
     */
    public static void exportExcelAndSelect(HttpServletResponse response, String fileName, Collection<?> list,
                                            Class<?> pojoClass, String[] dicts, int firstCol, int lastCol) throws IOException {
        if (StringUtils.isBlank(fileName)) {
            fileName = DateUtils.format(new Date());
        }
        ExportParams exportParams = new ExportParams();
//        exportParams.setSheetName("export");
        exportParams.setStyle(ExcelStyleUtil.class);
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
        Sheet sheet = workbook.getSheetAt(0);

        CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(1, 65535, firstCol, lastCol);
        DataValidationHelper helper = sheet.getDataValidationHelper();
        DataValidationConstraint constraint = helper.createExplicitListConstraint(dicts);
        DataValidation dataValidation = helper.createValidation(constraint, cellRangeAddressList);
        dataValidation.createErrorBox("错误", "请选择下拉框的值");
        dataValidation.setShowErrorBox(true);
        sheet.addValidationData(dataValidation);
        response.setCharacterEncoding("UTF-8");
        response.setHeader("content-Type", "application/vnd.ms-excel");
        response.setHeader("Content-Disposition",
                "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") + ".xls");
        ServletOutputStream out = response.getOutputStream();
        workbook.write(out);
        out.flush();
        workbook.close();
    }


    /**
     * 转换类型后导出
     *
     * @param response
     * @param fileName
     * @param sourceList
     * @param targetClass
     * @throws Exception
     */
    public static void exportExcelToTarget(HttpServletResponse response, String fileName, Collection<?> sourceList,
                                           Class<?> targetClass) throws Exception {
        List targetList = new ArrayList<>(sourceList.size());
        for (Object source : sourceList) {
            Object target = targetClass.newInstance();
            BeanUtils.copyProperties(source, target);
            targetList.add(target);
        }

        exportExcel(response, fileName, targetList, targetClass);
    }

    /**
     * 转换类型后导出
     *
     * @param response
     * @param fileName
     * @param sourceList
     * @param targetClass
     * @throws Exception
     */
    public static void exportExcelToTargetAndSelect(HttpServletResponse response, String fileName, Collection<?> sourceList,
                                                    Class<?> targetClass, String[] dicts, int firstCol, int lastCol) throws Exception {
        List targetList = new ArrayList<>(sourceList.size());
        for (Object source : sourceList) {
            Object target = targetClass.newInstance();
            BeanUtils.copyProperties(source, target);
            targetList.add(target);
        }

        exportExcelAndSelect(response, fileName, targetList, targetClass, dicts, firstCol, lastCol);
    }

    /**
     * 导出多个sheets
     *
     * @param response
     * @param fileName
     * @param sheetsList
     * @throws IOException
     */
    public static void exportManySheetExcel(HttpServletResponse response, String fileName,
                                            List<Map<String, Object>> sheetsList) throws IOException {
        if (StringUtils.isBlank(fileName)) {
            fileName = DateUtils.format(new Date());
        }
        Workbook workbook = ExcelExportUtil.exportExcel(sheetsList, ExcelType.HSSF);
        response.setCharacterEncoding("UTF-8");
        response.setHeader("content-Type", "application/vnd.ms-excel");
        response.setHeader("Content-Disposition",
                "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") + ".xls");
        ServletOutputStream out = response.getOutputStream();
        workbook.write(out);
        out.flush();
        workbook.close();
    }

    /**
     * 检查对象属性是否都为空
     *
     * @param object
     * @return
     */
    public static boolean checkObjAllFieldsIsNull(Object object) {
        if (null == object) {
            return true;
        }
        try {
            for (Field f : object.getClass().getDeclaredFields()) {
                f.setAccessible(true);
                if (f.get(object) != null && StringUtils.isNotBlank(f.get(object).toString())) {
                    return false;
                }
            }
        } catch (Exception e) {
            log.error("解析对象数据失败：", e);
            throw new CustomException(ResponseStatusEnum.COMMON_EXCEL_IMPORT_ERROR);
        }
        return true;
    }

    /**
     * 获取excel模板中的字段顺序
     *
     * @param templateClass
     * @return
     */
    public static String[] getExcelTitles(Class templateClass) {
        List<String> titles = new ArrayList<>();
        List<Excel> anns = new ArrayList<>();
        for (Field field : PoiPublicUtil.getClassFields(templateClass)) {
            if (field.isAnnotationPresent(Excel.class)) {
                Excel ann = field.getAnnotation(Excel.class);
                anns.add(ann);
            }
        }
        anns.sort(new Comparator<Excel>() {
            @Override
            public int compare(Excel excel, Excel t1) {
                return excel.orderNum().compareTo(t1.orderNum());
            }
        });
        titles = anns.stream().map(Excel::name).collect(Collectors.toList());
        return titles.toArray(new String[0]);
    }

    /**
     * 转换类型后导出
     *
     * @param response
     * @param fileName
     * @param sourceList
     * @param targetClass
     * @param titleRowsIndex 标题的行索引，从0计数
     * @param commentMap     批注map，key=列索引，从0计数；value=批注值
     * @param mustList     必填项
     * @throws Exception
     */
    public static void exportExcelToTargetAndMutliSelect(HttpServletResponse response, String fileName, Collection<?> sourceList,
                                                         Class<?> targetClass, Map<Integer, List<String>> map,
                                                         int titleRowsIndex, Map<Integer, String> commentMap, List<Integer> mustList) throws Exception {
        List targetList = new ArrayList<>(sourceList.size());
        for (Object source : sourceList) {
            Object target = targetClass.newInstance();
            BeanUtils.copyProperties(source, target);
            targetList.add(target);
        }
    
        exportExcelAndMutliSelect(response, fileName, targetList, targetClass, map, titleRowsIndex, commentMap, mustList);
    }

    /**
     *
     * 导出excel，生成多个下拉框
     * @param response
     * @param fileName
     * @param list
     * @param pojoClass
     * @param map   key 为excel中具体的列，下标从0开始 ，value为需要填充的内容
     * @param titleRowsIndex 标题的行索引，从0计数
     * @param commentMap     批注map，key=列索引，从0计数；value=批注值
     * @param mustList     必填项
     * @throws IOException
     */
    private static void exportExcelAndMutliSelect(HttpServletResponse response, String fileName, Collection<?> list, Class<?> pojoClass, Map<Integer, List<String>> map,
                                                  int titleRowsIndex, Map<Integer, String> commentMap, List<Integer> mustList) throws IOException {
        if (StringUtils.isBlank(fileName)) {
            fileName = DateUtils.format(new Date());
        }
        ExportParams exportParams = new ExportParams();
//        exportParams.setSheetName("export");
        exportParams.setStyle(ExcelStyleUtil.class);
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
        Sheet sheet = workbook.getSheetAt(0);
        for (Map.Entry<Integer, List<String>> entry : map.entrySet()) {
            CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(1, 65535, entry.getKey(),  entry.getKey());
            DataValidationHelper helper = sheet.getDataValidationHelper();
            DataValidationConstraint constraint = helper.createExplicitListConstraint(entry.getValue().toArray(new String[]{}));
            DataValidation dataValidation = helper.createValidation(constraint, cellRangeAddressList);
            dataValidation.createErrorBox("错误", "请选择下拉框中的值");
            dataValidation.setShowErrorBox(true);
            sheet.addValidationData(dataValidation);
        }
        
        // 设置批注和必填
        buildComment(workbook, titleRowsIndex, commentMap, mustList);
        
        response.setCharacterEncoding("UTF-8");
        response.setHeader("content-Type", "application/vnd.ms-excel");
        response.setHeader("Content-Disposition",
                "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") + ".xls");
        ServletOutputStream out = response.getOutputStream();
        workbook.write(out);
        out.flush();
        workbook.close();
    }
  
    
    /**
     * 添加批注
     *
     * @param workbook       工作簿
     * @param titleRowsIndex 标题的行索引，从0计数
     * @param commentMap     批注map，key=列索引，从0计数；value=批注值
     * @param mustList       必填项
     */
    public static void buildComment(Workbook workbook, int titleRowsIndex, Map<Integer, String> commentMap, List<Integer> mustList) {
        Sheet sheet = workbook.getSheetAt(0);
        //创建一个图画工具
        Drawing<?> drawing = sheet.createDrawingPatriarch();
        Row row = sheet.getRow(titleRowsIndex);
        if (!commentMap.isEmpty()) {
            for (Map.Entry<Integer, String> entry : commentMap.entrySet()) {
                Cell cell = row.getCell(entry.getKey());
                //创建批注
                Comment comment = drawing.createCellComment(newClientAnchor(workbook));
                //输入批注信息
                comment.setString(newRichTextString(workbook, entry.getValue()));
                //将批注添加到单元格对象中
                cell.setCellComment(comment);
                
               
            }
        }
        if (CollectionUtil.isNotEmpty(mustList)) {
            for (int i = 0; i < mustList.size(); i++) {
                Cell cell = row.getCell(mustList.get(i));
                //设置单元格背景颜色
                CellStyle cellStyle = getBaseCellStyle(workbook);;

                cellStyle.setFont(getTitleFont(workbook, FONT_SIZE_ELEVEN, false));
                //设置背景颜色
                cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
                //设置实心填充
                cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                cell.setCellStyle(cellStyle);
            }
        }
    }
    
    /**
     * 设置必填和批注标题字体样式
     *
     * @param size   字体大小
     * @param isBold 是否加粗
     * @return
     */
    private static Font getTitleFont(Workbook workbook, short size, boolean isBold) {
        Font font = workbook.createFont();
        //字体样式
        font.setFontName("宋体");
        //是否加粗
        font.setBold(isBold);
        // 设置字体颜色
        font.setColor(IndexedColors.RED.getIndex());
        //字体大小
        font.setFontHeightInPoints(size);
        return font;
    }
    
    /**
     * 基础样式
     *
     * @return
     */
    private static CellStyle getBaseCellStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        //下边框
        style.setBorderBottom(BorderStyle.THIN);
        //左边框
        style.setBorderLeft(BorderStyle.THIN);
        //上边框
        style.setBorderTop(BorderStyle.THIN);
        //右边框
        style.setBorderRight(BorderStyle.THIN);
        //水平居中
        style.setAlignment(HorizontalAlignment.CENTER);
        //上下居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        //设置自动换行
        style.setWrapText(true);
        return style;
    }
    
    private static RichTextString newRichTextString(Workbook workbook, String msg) {
        //xls
        if (workbook instanceof HSSFWorkbook) {
            return new HSSFRichTextString(msg);
        }
        //xlsx
        else {
            return new XSSFRichTextString(msg);
        }
    }
    
    private static ClientAnchor newClientAnchor(Workbook workbook) {
        //xls
        if (workbook instanceof HSSFWorkbook) {
            return new HSSFClientAnchor(0, 0, 0, 0, (short) 3, 3, (short) 5, 6);
        }
        //xlsx
        else {
            return new XSSFClientAnchor(0, 0, 0, 0, (short) 3, 3, (short) 5, 6);
        }
    }
}
